#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
PRESTO_HOME=/export/server/presto/bin/presto
${HIVE_HOME} -S -e "
-- todo 事实表（2张）
-- todo 客户意向事实表
create table edu_dwd.fact_customer_relationship_test
(
id                          int             comment '意向id',
create_date_time            string          comment '意向构建时间',
deleted                     int             comment '是否被删除（禁用）',
customer_id                 int             comment '所属客户id',
origin_type                 string          comment '数据来源（online-线上，offline-线下）',
itcast_school_id            int             comment '校区Id',
itcast_subject_id           int             comment '学科Id',
creator                     int             comment '创建人',
origin_channel              string          comment '来源渠道'
)
comment '客户意向事实表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

-- todo 客户线索事实表
create table edu_dwd.fact_customer_clue_test (
id                          int      comment '主键',
create_date_time            string   comment '创建时间',
deleted                     int      comment '是否被删除（禁用）',
customer_relationship_id    int      comment '客户关系id',
customer_state              string   comment '学员状态（new_customer-新学员，old_customer-老学员）'
)
comment '客户线索表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'snappy')
;

-----------------------------------------------------------------------------
-- todo 维度表
-- todo 客户静态信息表
create table edu_dwd.dim_customer_test (
id                       int      comment '主键自增',
customer_relationship_id int      comment '当前意向id',
area                     string   comment '所在区域'
)
comment '客户静态信息维度表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'snappy')
;


-- todo 线索申诉信息维度表
create table edu_dwd.dim_customer_appeal_test (
id                             int        comment '主键',
customer_relationship_first_id int        comment '第一条客户关系id',
appeal_status                  int        comment '申诉状态，0:待稽核 1:无效 2：有效'
)
comment '线索申诉信息维度表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'snappy')
;


-- todo  校区信息维度表
create table edu_dwd.dim_itcast_school_test (
id               int      comment '主键',
name             string   comment '校区名称'
)
comment '校区信息维度表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'snappy')
;


-- todo  学科信息维度表
create table edu_dwd.dim_itcast_subject_test (
id               int,
name             string   comment '学科名称'
)
comment '学科信息维度表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'snappy')
;


-- todo  员工部门信息维度表
create table edu_dwd.dim_scrm_department_test (
`id`               int      comment '部门id',
`name`             string   comment '部门名称'
)
comment '员工部门信息维度表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'snappy')
;



-- todo 员工信息维度表
create table edu_dwd.dim_employee_test
(
    id                  int     comment '员工id',
    tdepart_id          int     comment '直属部门'
)
comment '员工信息维度表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;
"

presto --catalog hive --server hadoop01:8090 --execute"
-- todo 插入数据到  edu_dwd.fact_customer_relationship
insert into edu_dwd.fact_customer_relationship_test
select
       id,
       create_date_time,
       deleted,
       customer_id,
       case when origin_type = 'NETSERVICE' or origin_type = 'PRESIGNUP' then 'online'
            else 'offline' end as origin_type,
       itcast_school_id,
       itcast_subject_id,
       creator,
       origin_channel
from edu_ods.customer_relationship_test
where deleted = 0;


-- todo 插入数据到  edu_dwd.fact_customer_clue
insert into edu_dwd.fact_customer_clue_test
select
id,
create_date_time,
deleted,
customer_relationship_id,
case when clue_state = 'VALID_NEW_CLUES' then 'new_customer'
     else 'old_customer' end as customer_state
from edu_ods.customer_clue_test
where deleted = 0
;

-- todo 插入数据到  edu_dwd.dim_customer
insert into edu_dwd.dim_customer_test
select
id,
customer_relationship_id,
area
from edu_ods.customer_test
where deleted = 0
;


-- todo 插入数据到  edu_dwd.dim_customer_appeal
insert into edu_dwd.dim_customer_appeal_test
select
id,
customer_relationship_first_id,
appeal_status
from edu_ods.customer_appeal_test
where deleted = 0
;


-- todo 插入数据到  edu_dwd.dim_employee
insert into edu_dwd.dim_employee_test
select
id,
tdepart_id
from edu_ods.employee_test
;


-- todo 插入数据到  edu_dwd.dim_itcast_school
insert into edu_dwd.dim_itcast_school_test
select
id,
name
from edu_ods.itcast_school_test
where deleted = 0
;

-- todo 插入数据到  edu_dwd.dim_itcast_subject
insert into edu_dwd.dim_itcast_subject_test
select
id,
name
from edu_ods.itcast_subject_test
where deleted = 0
;

-- todo 插入数据到  edu_dwd.dim_scrm_department
insert into edu_dwd.dim_scrm_department_test
select
id,
name
from edu_ods.scrm_department_test
where deleted = 0
;
"